#-*- coding:utf-8 -*-
'''
Created on 18.02.2010

@author: hu1salh1
'''
import MySQLdb
import sys
from thread import allocate_lock
class SQL_PAUK():
    '''
    Класс трансляции запросов в SQL на сервер
    '''
    def __init__(self):
        '''
        Constructor
        '''
        try:
            host,user,passwd,db=open("./connect.cfg").read().split(',')
            self.conn = MySQLdb.connect(host,user,passwd,db,use_unicode=True,charset='utf8')
            self.conn.set_character_set('cp1251')
            self.conn.query("SET NAMES utf8")
        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            sys.exit (1)
        self.cursor=self.conn.cursor()
        self.lock=allocate_lock()

    def ver_info(self):
        with self.lock:
            self.cursor.execute("SELECT VERSION()")
            row = self.cursor.fetchone()
            self.conn.commit()
            return row[0]

    def select_device(self,fields,table,sort_all,abc,row_cnt,limit_start,limit_count):
        with self.lock:
            if sort_all:
                sort='1'
                for k,v in sort_all.iteritems():
                    k='`'+k+'`'
                    v='"%'+v+'%"'
                    sort_line=' LIKE '.join((k,v))
                    sort+=' and '+sort_line
            else: sort='1'
     
            table='`'+table+'`'
            pole=','.join(['`'+x+'`' for x in fields])
            self.cursor.execute ("SELECT %s FROM %s WHERE %s ORDER BY `write_time` %s LIMIT %s , %s " 
                                 % (pole,table,sort,abc,limit_start,limit_count))
            requst=[]
            request_list=[]
            row = self.cursor.fetchall()
            for requst in row:
                request_list.append(requst)
            if row_cnt:
                return self.cursor.rowcount
            else: return request_list
        
    def insert_frame_device(self,param):
        # добавить блокировку БД для монопольного доступа и записи БД, не мешая поиску find_alert()
        with self.lock:
            self.cursor.executemany('''INSERT INTO `pauk_db`.`frame_device` (`id_frame_device`, `factory_id_device`, 
            `caption_device`, `name_frame`, `caption_frame`, `ip_frame`, `mac_frame`, `inventory_number_frame`, `write_time`)
                              values (%s, %s, %s, %s, %s, %s, %s, %s, %s)''', param)
            self.conn.commit()
    
    def find_alert(self):
        with self.lock:
            self.cursor.execute("SELECT `factory_id_device` FROM `frame_device`")
            row=set(self.cursor.fetchall())     #откидываем дубли
            for device in row:
                self.cursor.execute("SELECT `name_frame` FROM `frame_device` WHERE `factory_id_device` = %s ORDER BY `write_time` DESC LIMIT 0 , 2 ",device)
                wedro=self.cursor.fetchall()
                if wedro[0]!=wedro[1]:
                    print ".....achtung....."
            return
    
    def find_user_frame(self,frame_name):
        with self.lock:
            self.cursor.execute('''SELECT DISTINCT fio FROM `user` INNER JOIN `uses` ON `user`.`id_user`=`uses`.`id_user` 
            INNER JOIN `frame_device` ON `uses`.`name_frame`=`frame_device`.`name_frame` 
            WHERE `frame_device`.`name_frame` like %s''' % frame_name)
            row=self.cursor.fetchall()
            return row
    
    def find_frame_user(self,user_name):
        with self.lock:
            self.cursor.execute('''SELECT DISTINCT `caption_frame` FROM `frame_device` INNER JOIN `uses` ON 
            `frame_device`.`name_frame`=`uses`.`name_frame` INNER JOIN `user` ON `uses`.`id_user`=`user`.`id_user` 
            WHERE `user`.`fio` like %s''' % user_name)
            row=self.cursor.fetchall()
            return row
    
    def find_room_frame(self,frame_name):
        with self.lock:
            self.cursor.execute('''SELECT DISTINCT `number` FROM `room` INNER JOIN `frame_status` ON 
            `room`.`id_room`=`frame_status`.`id_room_to` INNER JOIN `frame_device` ON 
            `frame_status`.`id_frame_device`=`frame_device`.`id_frame_device` 
            WHERE `frame_device`.`caption_frame` like %s''' % frame_name)
            row=self.cursor.fetchall()
            return row
    
    def find_room_user(self,user_name):
        with self.lock:
            self.cursor.execute('''SELECT DISTINCT `number` FROM `room` INNER JOIN `frame_status` ON 
            `room`.`id_room`=`frame_status`.`id_room_to` INNER JOIN `frame_device` ON 
            `frame_status`.`id_frame_device`=`frame_device`.`id_frame_device` INNER JOIN `uses` ON
            `frame_device`.`name_frame`=`uses`.`name_frame` INNER JOIN `user` ON 
            `uses`.`id_user`=`user`.`id_user` WHERE `user`.`fio` like %s''' % user_name)
            row=self.cursor.fetchall()
            return row
